﻿CREATE TRIGGER [TRG_InventoryTransactionActivity]
    ON [dbo].[tblInventoryTransaction]
    AFTER DELETE, INSERT, UPDATE 
    AS 
    BEGIN
    	SET NOCOUNT ON

		-- If we are inside a data load then just return
		if CONTEXT_INFO() = 0x999
			return

		-- 2010-6-17 - Autobuild is taken care of in the AllocateInventory SP.
		---- If the reduction in inventory would reduce the inventory level to less than 0 then try
		---- building the autobuild product
		--declare cAutobuild cursor local for
		--select T2.InventoryID, T2.Qty - T1.Qty
		--from inserted T1
		--	join tblInventory T2 on T1.InventoryID = T2.InventoryID
		--	join tblProduct T3 on T2.ProductID = T3.ProductID
		--where T2.Qty + T1.Qty < 0
		--	and T3.AutoBuild = 1
		--declare @InventoryID int
		--declare @Qty int
		--open cAutobuild
		--fetch next from cAutobuild into @InventoryID, @Qty
		--while @@FETCH_STATUS = 0
		--begin
		--	exec spBuildComposite @InventoryID, @Qty 
			
		--	fetch next from cAutobuild into @InventoryID, @Qty
		--end
		--close cAutobuild
		--deallocate cAutobuild

		-- Update the inventory for additions to inventory when the status code changes to Committed
		update T2 set Qty = T2.Qty + T1.Qty
		from inserted T1
			join tblInventory T2 on T1.InventoryID = T2.InventoryID
			left outer join deleted T3 on T1.InventoryTransactionID = T3.InventoryTransactionID
		where T1.InventoryTransactionTypeCode not in ( 'U' ) -- Unusable returns don't affect inventory levels
			and T1.InventoryTransactionStatusCode = 'C'
			and coalesce(T3.InventoryTransactionStatusCode, '') <> 'C'
			and T1.Qty > 0

		-- Update the inventory for reductions to inventory when the status code goes
		-- to anything other than canceled or backorder.
		update T2 set Qty = T2.Qty + T1.Qty
		from inserted T1
			join tblInventory T2 on T1.InventoryID = T2.InventoryID
			left outer join deleted T3 on T1.InventoryTransactionID = T3.InventoryTransactionID
		where T1.InventoryTransactionStatusCode not in ( 'X', 'B' )
			and T1.InventoryTransactionStatusCode <> coalesce( T3.InventoryTransactionStatusCode, '' )
			and T1.Qty < 0
			and T3.InventoryTransactionID is null

		-- When reductions to inventory are canceled add the quantity back into inventory.
		-- Additions to inventory aren't applied until the transaction is committed, so cancelations to additions have no effect on inventory level.
		update T2 set Qty = T2.Qty - T1.Qty
		from inserted T1
			join tblInventory T2 on T1.InventoryID = T2.InventoryID
			join deleted T3 on T1.InventoryTransactionID = T3.InventoryTransactionID
		where T1.InventoryTransactionTypeCode not in ( 'U' ) -- Unusable returns don't affect inventory levels
			and T1.InventoryTransactionStatusCode = 'X'
			and T3.InventoryTransactionStatusCode not in ( 'X', 'B' )
			and T1.Qty < 0

		-- Don't allow inventory to be reduced to less than 0
		if exists ( select 1 from inserted T1
				join tblInventory T2 on T1.InventoryID = T2.InventoryID
			where T2.Qty < 0 )
			RAISERROR( 'Insufficient inventory exists at this location to process this inventory transaction.', 18, -1 )

		-- Record the InventoryTransaction change in the InventoryTransactionHistory table
		insert into tblInventoryTransactionHistory (InventoryTransactionID, PriorInventoryTransactionStatusCode, NewInventoryTransactionStatusCode, CreateDate, AgentID )
		select T1.InventoryTransactionID, T2.InventoryTransactionStatusCode, T1.InventoryTransactionStatusCode, getdate(), 1
		from inserted T1
			left outer join deleted T2 on T1.InventoryTransactionID = T2.InventoryTransactionID

		declare @InventoryCostUpdates table ( InventoryID int not null, UnitCost money null, Qty int not null, Qty1 int null, InventoryCostID int null )
		declare @TransactionData table ( InventoryID int, Amount money )

		-- Record a journal entry for the inventory transaction
		declare cInventoryCost cursor local for
		select T1.InventoryID, T1.OrderID, T4.OwnerID,
			abs( sum( coalesce( T1.UnitCost, T4.UnitCost, 0 ) * T1.Qty ) )
		from inserted T1
			left outer join deleted T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
			join tblInventory T3 on T1.InventoryID = T3.InventoryID
			join tblProduct T4 on T3.ProductID = T4.ProductID
		where T1.InventoryTransactionTypeCode not in ( 'U' ) -- Unusable returns don't affect accounting
			and T1.InventoryTransactionStatusCode = 'C'
			and coalesce(T2.InventoryTransactionStatusCode, '') <> 'C'
			and coalesce( T1.UnitCost, T4.UnitCost, 0 )  > 0
		group by T1.InventoryID, T1.OrderID, T4.OwnerID
		declare @OrderID int
		declare @OwnerID int
		declare @InventoryID int
		declare @TransactionCost money
		open cInventoryCost
		fetch next from cInventoryCost into @InventoryID, @OrderID, @OwnerID, @TransactionCost
		while @@FETCH_STATUS = 0
		begin
			insert into tblJournal( JournalEntryCode, Amount, OwnerID )
			values ( 'I', @TransactionCost, @OwnerID )

			--update T2 set TransactionID = @TransactionID
			--from inserted T1
			--	join tblInventoryTransaction T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
			--where T1.InventoryID = @InventoryID

			fetch next from cInventoryCost into @InventoryID, @OrderID, @OwnerID, @TransactionCost
		end
		close cInventoryCost
		deallocate cInventoryCost


		-- Updates to the UnitCost table and creation of the Transaction records happen when the Inventory Transaction
		-- is complete.  Completed Inventory Transactions cannot be reversed.
		insert into @InventoryCostUpdates( InventoryID, Qty, UnitCost )
		select T1.InventoryID, T1.Qty, coalesce( T1.UnitCost, T4.UnitCost, 0 ) 
		from inserted T1
			left outer join deleted T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
			join tblInventory T3 on T1.InventoryID = T3.InventoryID
			join tblProduct T4 on T3.ProductID = T4.ProductID
		where T1.InventoryTransactionTypeCode not in ( 'U' ) -- Unusable returns don't affect inventory levels
			and T1.InventoryTransactionStatusCode = 'C'
			and coalesce(T2.InventoryTransactionStatusCode, '') <> 'C'

		-- Make sure that there exist tblInventoryCost records for the referenced Inventory at the given cost
		insert into tblInventoryCost ( InventoryID, UnitCost, Qty )
		select distinct T1.InventoryID, T1.UnitCost, 0
		from @InventoryCostUpdates T1
			left outer join tblInventoryCost T2 on T1.InventoryID = T2.InventoryID 
				and T1.UnitCost = T2.UnitCost
		where T2.InventoryCostID is null

		-- Record the quantity update in InventoryCostUpdates 
		while exists ( select 1 from @InventoryCostUpdates where Qty <> 0 )
		begin
			-- Record the amount that we can update the InventoryCost records with matching costs
			update T1 set Qty1 = case when T2.Qty + T1.Qty > 0 then T1.Qty else -T2.Qty end,
				InventoryCostID = T2.InventoryCostID
			from @InventoryCostUpdates T1
				join tblInventoryCost T2 on T1.InventoryID = T2.InventoryID 
					and T1.UnitCost = T2.UnitCost

			-- Update available inventory in the InventoryCost table with matching costs
			update T2 set Qty = T2.Qty + T1.Qty1
			from @InventoryCostUpdates T1
				join tblInventoryCost T2 on T1.InventoryCostID = T2.InventoryCostID

			-- Update @InventoryCostUpdates to reflect the amounts applied to the InventoryCost table
			update @InventoryCostUpdates set Qty = Qty - Qty1
			update @InventoryCostUpdates set Qty1 = 0

			-- Update the UnitCost field in @InventoryCostUpdates so that on the next pass
			-- we will adjust stock in an InventoryCost record that has stock available.
			-- We look for the InventoryCost record matching the InventoryID which has the smallest
			-- quantity greater than 0.  This way we reduce the stock of the InventoryCost record
			-- with the smallest qty first.
			update T1 set UnitCost = T2.UnitCost
			from @InventoryCostUpdates T1
				join ( select InventoryID, UnitCost
					from tblInventoryCost T1
						join ( select MIN(T2.InventoryCostID ) as InventoryCostID
							from ( select InventoryID, min(Qty) as qty
								from tblInventoryCost
								where Qty > 0
								group by InventoryID ) as T1
								join tblInventoryCost T2 on T1.InventoryID = T2.InventoryID
									and T1.qty = T2.Qty
							group by T2.InventoryID ) as T2
								on T1.InventoryCostID = T2.InventoryCostID ) as T2 on T1.InventoryID = T2.InventoryID
			where T1.Qty <> 0

			-- If there is no InventoryCost record with available inventory to update then exit the loop.
			if not exists ( select 1 from @InventoryCostUpdates T1
				join tblInventoryCost T2 on T1.InventoryID = T2.InventoryID
					and T1.UnitCost = T2.UnitCost
				where T1.Qty + T2.Qty > 0 
					or T2.Qty > 0 )
				break
		end
    END
